using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Collections;
using System.Collections.Generic;

public partial class StoredProcedures
{
	protected static bool RunSql(string sSql, ref SqlConnection sqlConn, ref SqlCommand sqlCmd, ref SqlDataReader sqlReader, string sDest, out string sRes)
	{
		bool bRes = false ;
		try
		{
			if (null == sqlConn)
				sqlConn = new SqlConnection("context connection=true");
			if (null == sqlConn)
				sRes = "Context connection failed";
			else 
			{
				if (sqlConn.State != ConnectionState.Open)
					sqlConn.Open() ;
				if (sqlConn.State != ConnectionState.Open)
					sRes = "ConnectionState==" + sqlConn.State.ToString();
				else
				{
					if (null == sqlCmd)
						sqlCmd = new SqlCommand();
					if (null == sqlCmd)
						sRes = "SqlCommand failed";
					else
					{
						sqlCmd.CommandType = CommandType.Text;
						sqlCmd.Connection = sqlConn;
						sqlCmd.CommandText = sSql;
						if (null != sqlReader)
							sqlReader.Close();
						sqlReader = sqlCmd.ExecuteReader(CommandBehavior.SequentialAccess);
						if (sqlReader == null)
							sRes = "ExecuteReader failed";
						else if (sqlReader.FieldCount == 0)
							sRes = "No fields";
						else
						{
							sRes = "No error";
							bRes = true;
						}
					}
				}
			}
			if (!bRes)
				RunSqlCleanup(ref sqlConn, ref sqlCmd, ref sqlReader, sDest);
		}
		catch (Exception ex)
		{
			sRes = "Exception";
			WriteToFile(sDest + ".ex.txt", "RunSql:" + ex.Message) ;
		}
		return bRes ;
	}
	protected static void RunSqlCleanup(ref SqlConnection sqlConn, ref SqlCommand sqlCmd, ref SqlDataReader sqlReader, string sDest)
	{
		try
		{
			if (null != sqlReader)
			{
				//sqlReader.Dispose() ;
				sqlReader = null ;
			}
			if (null != sqlCmd)
			{
				//sqlCmd.Dispose();
				sqlCmd = null;
			}
			if (null != sqlConn)
			{
				if (sqlConn.State == ConnectionState.Open)
					sqlConn.Close();
				//sqlConn.Dispose(); // Not allowed!
				sqlConn = null;
			}
		}
		catch (Exception ex)
		{
			WriteToFile(sDest + ".ex.txt", "RunSqlCleanup:" + ex.Message + "\r\n" + ex.StackTrace);
		}
	}
	protected static void GetFieldLengthsAndRecordCount(SqlDataReader sqlReader, ref List<int> lengthOfFields, out UInt32 recordCount, string sDest)
	{
		recordCount = 0;
		try
		{
			lengthOfFields = new List<int>(sqlReader.FieldCount);
			for (int i = 0; i < sqlReader.FieldCount; i++)
				lengthOfFields.Add(0);
			while (sqlReader.Read())
			{
				++recordCount;
				for (int i = 0; i < sqlReader.FieldCount; i++)
				{
					if (!sqlReader.IsDBNull(i))
					{
						int iLen = sqlReader.GetString(i).Length;
						if (iLen > (Int16)(lengthOfFields[i]))
							lengthOfFields[i] = iLen;
					}
				}
			}
		}
		catch (Exception ex)
		{
			WriteToFile(sDest + ".ex.txt", "GetFieldLengthsAndRecordCount:" + ex.Message + "\r\n" + ex.StackTrace);
		}
	}
	protected static void WriteRecords(SqlDataReader sqlReader, List<int>lengthOfFields, BinaryWriter writer, Encoding encoding, string sDest)
	{
		int i = 0;
		try
		{
			while (sqlReader.Read())
			{
				writer.Write('\0');
				for (i = 0; i < sqlReader.FieldCount; i++)
				{
					string sValue = (sqlReader.IsDBNull(i)) ? "" : sqlReader.GetString(i);
					writer.Write(encoding.GetBytes(sValue));
					int iPad = sValue.Length;
					if (iPad < lengthOfFields[i])
					{
						writer.Write(' ');
						for (iPad = iPad + 1; iPad < lengthOfFields[i]; iPad++)
							writer.Write('\0');
					}
				}
			}
		}
		catch (Exception ex)
		{
			WriteToFile(sDest + ".ex.txt", 
				"WriteRecords:" + ex.Message + "\r\n" + 
				ex.StackTrace + "\r\n" +
				lengthOfFields.Count.ToString() + i.ToString() + sqlReader.FieldCount.ToString());
		}
	}
	protected static void CopyUIntToByteArray(UInt32 value, ref byte[] bA, UInt32 index, string sDest)
	{
		try
		{
			UInt32 v = value;
			bA[index] = (byte)(v % 256);
			v >>= 8;
			bA[index + 1] = (byte)(v % 256);
			v >>= 8;
			bA[index + 2] = (byte)(v % 256);
			v >>= 8;
			bA[index + 3] = (byte)(v);
		}
		catch (Exception ex)
		{
			WriteToFile(sDest + ".ex.txt", "CopyUIntToByteArray:" + ex.Message + "\r\n" + ex.StackTrace);
		}
	}
	protected static void WriteHeader(SqlDataReader sqlReader, List<int>lengthOfFields, UInt32 recordCount, BinaryWriter writer, Encoding ascii, string sDest)
	{
		try
		{
			byte[] bFieldDef = new byte[32];
			for (int i = 0; i < 32; i++)
				bFieldDef[i] = 0;
			int rowSize = 1;
			for (int iField = 0; iField < sqlReader.FieldCount; iField++)
				rowSize += lengthOfFields[iField];
			CopyUIntToByteArray(recordCount, ref bFieldDef, 4, sDest); // Record count
			CopyUIntToByteArray((UInt32)((1 + sqlReader.FieldCount) * 32 + 1), ref bFieldDef, 8, sDest); // Header size (really 2 bytes)
			CopyUIntToByteArray((UInt32)rowSize, ref bFieldDef, 10, sDest); // Row size
			writer.Write(bFieldDef);

			for (int iField = 0; iField < sqlReader.FieldCount; iField++)
			{
				string sFieldName = sqlReader.GetName(iField);
				for (int i = 0; i < 32; i++)
					bFieldDef[i] = (byte)((sFieldName.Length > i) ? sFieldName[i] : 0);
				bFieldDef[16] = (byte)(lengthOfFields[iField]);
				writer.Write(bFieldDef);
			}
			writer.Write((byte)0x0D);
		}
		catch (Exception ex)
		{
			WriteToFile(sDest + ".ex.txt", "WriteHeader:" + ex.Message + "\r\n" + ex.StackTrace);
		}
	}
	[Microsoft.SqlServer.Server.SqlProcedure]
	public static void ISearchExportToNative(string sSql, string sDest)
	{
		SqlConnection sqlConn = null;
		SqlCommand sqlCmd = null;
		SqlDataReader sqlReader = null;
		List<int> lengthOfFields = null;
		UInt32 recordCount = 0;
		Encoding ascii = Encoding.GetEncoding(850);
		string sRes = "";
		try
		{
			if (!RunSql(sSql, ref sqlConn, ref sqlCmd, ref sqlReader, sDest, out sRes))
				WriteToFile(sDest + ".txt", "Sql failed\r\n" + sSql);
			else
			{
				GetFieldLengthsAndRecordCount(sqlReader, ref lengthOfFields, out recordCount, sDest);
				if (!RunSql(sSql, ref sqlConn, ref sqlCmd, ref sqlReader, sDest, out sRes))
					WriteToFile(sDest + ".txt", "Sql 2nd failed\r\n" + sSql);
				else
				{
					using (FileStream fs = new FileStream(sDest, FileMode.OpenOrCreate, FileAccess.Write))
					{
						using (BinaryWriter writer = new BinaryWriter(fs))
						{
							WriteHeader(sqlReader, lengthOfFields, recordCount, writer, ascii, sDest);
							WriteRecords(sqlReader, lengthOfFields, writer, ascii, sDest);
							if (fs.Length > fs.Position)
								fs.SetLength(fs.Position);
						}
					}
					RunSqlCleanup(ref sqlConn, ref sqlCmd, ref sqlReader, sDest);
				}
			}
		}
		catch (Exception ex)
		{
			WriteToFile(sDest + ".ex.txt", "ISearchExportToNative:" + ex.Message + "\r\n" + ex.StackTrace);
		}
	}
	protected static void WriteToFile(string sDest, string s)
	{
		using (FileStream fs = new FileStream(sDest, FileMode.OpenOrCreate, FileAccess.Write))
		{
			using (BinaryWriter writer = new BinaryWriter(fs))
			{
				writer.Write((new ASCIIEncoding()).GetBytes(s));
				// Truncate rest of file...
				if (fs.Length > fs.Position)
					fs.SetLength(fs.Position);
			}
		}
	}
};
